﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using StockFinder.Model;

namespace StockFinder.DataAccess
{
    public class ResultsDataAccess : DataAccessBase
    {
        private const string INSERT_JUST_OUT_OF_BASE_PROCEDURE = "dbo.InsertJustBrokenOut";
        private const string INSERT_PRICE_WITH_WINDOW = "dbo.InsertPriceWithinWindow";
        private const string INSERT_AT_ALL_TIME_HIGH_PROCEDURE = "dbo.InsertAtAllTimeHigh";
        private const string INSERT_CLOSE_TO_BREAKING_OUT_PROCEDURE = "dbo.InsertCloseToBreakout";
        private const string DELETE_JUST_BROKEN_OUT_PROCEDURE = "dbo.DeleteAllJustBrokenOut";
        private const string INSERT_BIGGEST_UP_VOLUME_DAY_RESULT = "dbo.InsertBiggestVolumeUpDayResult";
        private const string GET_JUST_BROKEN_OUT_RESULTS_FOR_DATE = "dbo.GetJustBrokenOutResultsForDate";
        private const string GET_PRICE_WITHIN_WINDOW_RESULTS_FOR_DATE = "dbo.GetPriceWithinWindowResultsForDate";

        public void InsertBiggestVolumeUpDayResult(string symbol, int daysOfPrices, decimal baseDepth, DateTime mostRecentPriceDate, DateTime runDate)
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(INSERT_BIGGEST_UP_VOLUME_DAY_RESULT, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@SymbolName", symbol);
                command.Parameters.AddWithValue("@DaysOfPrices", daysOfPrices);
                command.Parameters.AddWithValue("@baseDepth", baseDepth);
                command.Parameters.AddWithValue("@mostRecentPriceDate", mostRecentPriceDate);
                command.Parameters.AddWithValue("@RunDate", runDate);
            }
        }

        public void InsertPriceWithinWindow(string symbol, int baseLength, decimal baseDepth, decimal baseLow, DateTime baseLowDate,
            DateTime previousHighDate, decimal previousHigh, DateTime lastPriceDate, int daysOfPrices, DateTime runDate)
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(INSERT_PRICE_WITH_WINDOW, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@Symbol", symbol);
                command.Parameters.AddWithValue("@BaseLength", baseLength);
                command.Parameters.AddWithValue("@BaseDepth", baseDepth);
                command.Parameters.AddWithValue("@BaseLow", baseLow);
                command.Parameters.AddWithValue("@BaseLowDate", baseLowDate);
                command.Parameters.AddWithValue("@PreviousHighDate", previousHighDate);
                command.Parameters.AddWithValue("@PreviousHigh", previousHigh);
                command.Parameters.AddWithValue("@LastPriceDate", lastPriceDate);
                command.Parameters.AddWithValue("@DaysOfPrices", daysOfPrices);
                command.Parameters.AddWithValue("@RunDate", runDate);

                command.ExecuteNonQuery();
            }
        }

        public void InsertJustBrokenOut(string symbol, int baseLength, decimal baseDepth, decimal baseLow, DateTime baseLowDate,
            DateTime previousHighDate, decimal previousHigh, DateTime lastPriceDate, int daysOfPrices, DateTime runDate)
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(INSERT_JUST_OUT_OF_BASE_PROCEDURE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@Symbol", symbol);
                command.Parameters.AddWithValue("@BaseLength", baseLength);
                command.Parameters.AddWithValue("@BaseDepth", baseDepth);
                command.Parameters.AddWithValue("@BaseLow", baseLow);
                command.Parameters.AddWithValue("@BaseLowDate", baseLowDate);
                command.Parameters.AddWithValue("@PreviousHighDate", previousHighDate);
                command.Parameters.AddWithValue("@PreviousHigh", previousHigh);
                command.Parameters.AddWithValue("@LastPriceDate", lastPriceDate);
                command.Parameters.AddWithValue("@DaysOfPrices", daysOfPrices);
                command.Parameters.AddWithValue("@RunDate", runDate);

                command.ExecuteNonQuery();
            }
        }        

        public void DeleteAllJustBrokenOut()
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(DELETE_JUST_BROKEN_OUT_PROCEDURE, connection) { CommandType = CommandType.StoredProcedure })
            {
                command.ExecuteNonQuery();
            }
        }

        public void InsertAtAllTimeHigh(int symbolId)
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(INSERT_AT_ALL_TIME_HIGH_PROCEDURE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@SymbolId", symbolId);

                command.ExecuteNonQuery();
            }
        }

        public void InsertCloseToBreakout(int symbolId)
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(INSERT_CLOSE_TO_BREAKING_OUT_PROCEDURE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@SymbolId", symbolId);

                command.ExecuteNonQuery();
            }
        }

        #region Getters

        public List<PriceAnalysisResult> GetJustBrokenOutResultsForDate(DateTime runDate)
        {
            List<PriceAnalysisResult> results = null;

            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(GET_JUST_BROKEN_OUT_RESULTS_FOR_DATE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.Parameters.AddWithValue("@RunDate", runDate);

                    SqlDataReader reader = command.ExecuteReader();

                    results = new List<PriceAnalysisResult>();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            results.Add(new PriceAnalysisResult()
                            {
                                SymbolName = reader["Symbol"].ToString(),
                                DaysOfPrices = (int)reader["DaysOfPrices"],
                                BaseLength = (int)reader["BaseLength"],
                                BaseDepth = (decimal)reader["BaseDepth"],
                                BaseLow = (decimal)reader["BaseLowPrice"],
                                BaselowDate = (DateTime)reader["BaseLowDate"],
                                PreviousHigh = (decimal)reader["PreviousHighPrice"],
                                PreviousHighDate = (DateTime)reader["PreviousHighDate"]
                            });
                        }
                    }
                }
            }

            return results;
        }

        public List<PriceAnalysisResult> GetPriceWithinWindowResultsForDate(DateTime runDate)
        {
            List<PriceAnalysisResult> results = null;

            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(GET_PRICE_WITHIN_WINDOW_RESULTS_FOR_DATE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.Parameters.AddWithValue("@RunDate", runDate);

                    SqlDataReader reader = command.ExecuteReader();

                    results = new List<PriceAnalysisResult>();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            results.Add(new PriceAnalysisResult()
                            {
                                SymbolName = reader["Symbol"].ToString(),
                                DaysOfPrices = (int)reader["DaysOfPrices"],
                                BaseLength = (int)reader["BaseLength"],
                                BaseDepth = (decimal)reader["BaseDepth"],
                                BaseLow = (decimal)reader["BaseLowPrice"],
                                BaselowDate = (DateTime)reader["BaseLowDate"],
                                PreviousHigh = (decimal)reader["PreviousHighPrice"],
                                PreviousHighDate = (DateTime)reader["PreviousHighDate"]
                            });
                        }
                    }
                }
            }

            return results;
        }

        #endregion
    }
}
